package connect;

import bean.CompteBean;
import bean.LoginBean;
import entity.Statistique;
import java.io.OutputStream;
import java.sql.*;
import java.util.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.EJB;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;
import oracleObject.*;
import session.StatistiqueFacadeLocal;

/**
 *
 * @author Mély
 */
@ManagedBean
@SessionScoped
public class OracleConnect {

    private String login;
    private String password;
    @EJB
    private StatistiqueFacadeLocal statistiqueBean;
    private Connection connection;
    private String roleDeConnexion;
    private List<OracleUser> allUser;
    private List<TableSpace> allTableSpace;
    private int countObjectByUser;
    @ManagedProperty(value = "#{compteBean}")
    private CompteBean compteBean;
    @ManagedProperty(value = "#{loginBean}")
    private LoginBean loginBean;
    private List<InstanceOracle> allInstanceOracle;
    private List<DbLinksOracle> allDbLinksOracle;
    private List<ObjectOracle> allObjectForCurrentUser;
    private List<TableOracle> allTableOracleForCurrentUser;
    private List<MethodeOracle> allMethodeOracleForCurrentUser;
    private List<TriggerOracle> allTriggerOracleForCurrentUser;
    private List<ViewOracle> allViewOracleForCurrentUser;
    private String myQuery;
    private TableOracle currentTable;
    private MethodeOracle currentMethode;
    private ViewOracle currentView;
    private TriggerOracle currentTrigger;
    private String backupColdPath;
    private String backupHotPath;
    private String startupPath;

    public OracleConnect() {
    }

    /*
     * Connection à la base de données courante
     */
    public String connect() {
        Statistique stat = new Statistique(this.getLoginBean().getUser().getUserid(), this.getCompteBean().getCurrent().getId(), new Date());
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String portNumber = String.valueOf(this.getCompteBean().getCurrent().getPortNumber());
            connection = DriverManager.getConnection("jdbc:oracle:thin:@"
                    + this.getCompteBean().getCurrent().getServer()
                    + ":"
                    + portNumber
                    + ":"
                    + this.getCompteBean().getCurrent().getName(), this.getLogin(), this.getPassword());

            stat.setSuccess(Boolean.TRUE);
            statistiqueBean.create(stat);
            if(this.isAdmin()) {
                return "/ConnectionOracle/oracleConnectionAdministrateur.jsf?faces-redirect=true";
            }
            else if(this.isDeveloppeur()) {
                return "/ConnectionOracle/oracleConnectionDeveloppeur.jsf?faces-redirect=true";
            }        
        } catch (SQLException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        //reset();
        //password = null;
        stat.setSuccess(Boolean.FALSE);
        statistiqueBean.create(stat);
        return "/ConnectionOracle/oracleConnectionError.jsf?faces-redirect=true";
    }

    /*
     * Déconnection à la base de données courante en passant par un clic sur la
     * page web
     */
    public String deconnectFromWeb() {
        reset();
        if (deconnect()) {
            return "/ConnectionOracle/oracleDisconnect.jsf?faces-redirect=true";
        }
        return "/ConnectionOracle/oracleDisconnectError.jsf?faces-redirect=true";
    }

    /**
     *
     * @return true if deconnect success, false if deconnect error
     */
    public boolean deconnect() {
        try {
            this.getConnection().close();
            this.connection = null;
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public void reset() {
        login = null;
        password = null;
        myQuery = null;
        roleDeConnexion = null;
    }

    public String getLogin() {
        return login;
    }

    public void setLogin(String login) {
        this.login = login;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public CompteBean getCompteBean() {
        return compteBean;
    }

    public void setCompteBean(CompteBean compteBean) {
        this.compteBean = compteBean;
    }

    /*
     *
     * REQUETES ADMINISTRATEUR
     *
     */
    /*
     * Renvoie la liste des utilisateurs
     */
    public List<OracleUser> getAllUser() {
        allUser = new ArrayList<OracleUser>();
        String query = "SELECT username, user_id, account_status, "
                + "default_tablespace, temporary_tablespace, "
                + "authentication_type, profile, created, expiry_date "
                + "FROM dba_users "
                + "ORDER BY username";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                OracleUser oracleUser = new OracleUser();
                String username = res.getString(1);
                oracleUser.setName(username);
                oracleUser.setId(res.getInt(2));
                oracleUser.setAccountStatus(res.getString(3));
                oracleUser.setDefaultTablespace(res.getString(4));
                oracleUser.setTemporaryTablespace(res.getString(5));
                oracleUser.setAuthenticationType(res.getString(6));
                oracleUser.setProfile(res.getString(7));
                oracleUser.setCreatedDate(res.getString(8));
                oracleUser.setExpiryDate(res.getString(9));
                oracleUser.setPrivileges(this.getPrivilegeByUser(username));
                oracleUser.setRoles(this.getRoleByUser(username));
                allUser.add(oracleUser);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allUser;
    }

    public void setAllUser(List<OracleUser> allUser) {
        this.allUser = allUser;
    }

    public List<ObjectOracle> getObjectByUser(String nameUser) {
        List objectByUser = new ArrayList<ObjectOracle>();
//        if (this.isAdmin()) {
            String query = "SELECT object_type, COUNT(object_type) "
                    + "FROM all_objects "
                    + "WHERE owner = '"
                    + nameUser
                    + "' GROUP BY object_type";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    objectByUser.add(new ObjectOracle(res.getString(1), Integer.parseInt(res.getString(2))));
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return objectByUser;
    }

    public List<String> getPrivilegeByUser(String nameUser) {
        List privilegeByUser = new ArrayList<String>();
        String query = "SELECT privilege "
                + "FROM dba_sys_privs "
                + "WHERE UPPER(grantee)=UPPER('"
                + nameUser
                + "')";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                privilegeByUser.add(res.getString(1));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return privilegeByUser;
    }

    public List<String> getRoleByUser(String nameUser) {
        List roleByUser = new ArrayList<String>();
        String query = "SELECT granted_role "
                + "FROM dba_role_privs "
                + "WHERE UPPER(grantee)=UPPER('"
                + nameUser
                + "')";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                roleByUser.add(res.getString(1));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return roleByUser;
    }

    public int getCountObjectByUser(String nameUser) {
        countObjectByUser = 0;
//        if (this.isAdmin()) {
            String query = "SELECT SUM(COUNT(object_type)) "
                    + "FROM all_objects "
                    + "WHERE owner='"
                    + nameUser
                    + "' GROUP BY object_type";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    countObjectByUser = res.getInt(1);
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return countObjectByUser;
    }

    public List<TableSpace> getAllTableSpace() {
        allTableSpace = new ArrayList<TableSpace>();
//        if (this.isAdmin()) {
            String query = "SELECT a.tablespace_name, "
                    + "a.totale, "
                    + "ROUND(b.occupe,2), "
                    + "ROUND(c.libre,2), "
                    + "ROUND((100*b.occupe)/a.totale,2) AS pourcentage, "
                    + "a.autoextensible, "
                    + "a.file_name, "
                    + "a.taille_maximale "
                    + "FROM "
                    + "(SELECT tablespace_name, SUM(bytes)/1024/1024 AS totale, file_name, "
                    + "ROUND(maxbytes/1024/1024,2) AS taille_maximale, autoextensible "
                    + "FROM dba_data_files GROUP BY tablespace_name, file_name, maxbytes, autoextensible)a, "
                    + "(SELECT tablespace_name, SUM(bytes)/1024/1024 AS occupe "
                    + "FROM dba_segments group by tablespace_name)b, "
                    + "(SELECT tablespace_name, SUM(bytes)/1024/1024 AS libre "
                    + "FROM dba_free_space GROUP BY tablespace_name)c "
                    + "WHERE b.tablespace_name=a.tablespace_name "
                    + "AND c.tablespace_name=b.tablespace_name "
                    + "ORDER BY a.tablespace_name";

            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    TableSpace tableSpace = new TableSpace();
                    tableSpace.setName(res.getString(1));
                    tableSpace.setTotal(res.getInt(2));
                    tableSpace.setBusy(res.getDouble(3));
                    tableSpace.setFree(res.getDouble(4));
                    tableSpace.setPercent(res.getDouble(5));
                    tableSpace.setAutoextensible(res.getString(6));
                    tableSpace.setFileName(res.getString(7));
                    tableSpace.setMaxSize(res.getDouble(8));
                    allTableSpace.add(tableSpace);
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allTableSpace;
    }

    public List<InstanceOracle> getAllInstanceOracle() {
        allInstanceOracle = new ArrayList<InstanceOracle>();
//        if (this.isAdmin()) {
            String query = "SELECT instance_number, "
                    + "instance_name, "
                    + "host_name, "
                    + "version, "
                    + "instance_role, "
                    + "status, "
                    + "database_status, "
                    + "active_state, "
                    + "startup_time FROM v$instance";

            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    InstanceOracle instanceOracle = new InstanceOracle();
                    instanceOracle.setInstanceNumber(res.getInt(1));
                    instanceOracle.setInstanceName(res.getString(2));
                    instanceOracle.setHostName(res.getString(3));
                    instanceOracle.setVersion(res.getString(4));
                    instanceOracle.setInstanceRole(res.getString(5));
                    instanceOracle.setStatus(res.getString(6));
                    instanceOracle.setDataBaseStatus(res.getString(7));
                    instanceOracle.setActiveState(res.getString(8));
                    instanceOracle.setStartupTime(res.getString(9));
                    allInstanceOracle.add(instanceOracle);
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allInstanceOracle;
    }
    
    public List<DbLinksOracle> getAllDbLinksOracle() {
        allDbLinksOracle = new ArrayList<DbLinksOracle>();
        String query = "SELECT * "
                + "FROM dba_db_links";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                DbLinksOracle dbLinksOracle = new DbLinksOracle();
                dbLinksOracle.setOwner(res.getString(1));
                dbLinksOracle.setLink(res.getString(2));
                dbLinksOracle.setUsername(res.getString(3));
                dbLinksOracle.setHost(res.getString(4));
                dbLinksOracle.setCreated(res.getString(5));
                allDbLinksOracle.add(dbLinksOracle);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return allDbLinksOracle;
    }
    
    public String getDbName() {
        String query = "SELECT value FROM v$parameter WHERE name = 'db_name'";
        String dbName = "";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                dbName = res.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dbName;
    }
    
    public void saveBackupColdScript() {
        String dbName = this.getDbName();
            FacesContext context = FacesContext.getCurrentInstance();
            try {
                if (System.getProperty("file.separator").charAt(0) != (this.getStartupPath().charAt(this.getStartupPath().length() - 1))) {
                    String backslash = System.getProperty("file.separator");
                    startupPath += backslash;
                }
                
                if (System.getProperty("file.separator").charAt(0) != (this.getBackupColdPath().charAt(this.getBackupColdPath().length() - 1))) {
                    String backslash = System.getProperty("file.separator");
                    backupColdPath += backslash;
                }
                
                String filename = "saveCold.sql";
                HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
                response.setHeader("Content-disposition", "attachment; filename=" + filename);
                response.setContentType("application/octet-stream"); //fill in contentType
                OutputStream os = response.getOutputStream();
                
                os.write("shutdown immediate;".getBytes());
                os.write("\r\n".getBytes());
                String startup = "startup pfile=" + this.getStartupPath() + "init" + dbName + ".ora;";
                String startupRestrict = startup.substring(0,startup.length()-1) + " restrict;";
                os.write(startupRestrict.getBytes());
                os.write("\r\n".getBytes());
                String spool = "spool '" + this.getBackupColdPath() + "sauve.cmd';";
                os.write(spool.getBytes());
                os.write("\r\n".getBytes());
                String copyLogFile = "select 'host copy ' || member || ' " + this.getBackupColdPath() + "' from v$logfile;";
                os.write(copyLogFile.getBytes());
                os.write("\r\n".getBytes());
                String copyDataFile = "select 'host copy ' || name || ' " + this.getBackupColdPath() + "' from v$datafile;";
                os.write(copyDataFile.getBytes());
                os.write("\r\n".getBytes());
                String copyControlFile = "select 'host copy ' || name || ' " + this.getBackupColdPath() + "' from v$controlfile;";
                os.write(copyControlFile.getBytes());
                os.write("\r\n".getBytes());
                os.write("spool off;".getBytes());
                os.write("\r\n".getBytes());
                os.write("shutdown immediate;".getBytes());
                os.write("\r\n".getBytes());
                os.write("-- Lancement du script de sauvegarde".getBytes());
                os.write("\r\n".getBytes());
                String callSauve = "call " + this.getBackupColdPath() + "sauve.cmd";
                os.write(callSauve.getBytes());
                os.write("\r\n".getBytes());
                String stopService = "net stop OracleService" + dbName;
                os.write(stopService.getBytes());
                os.write("\r\n".getBytes());
                String startService = "net start OracleService" + dbName;
                os.write(startService.getBytes());
                os.write("\r\n".getBytes());
                os.write(startup.getBytes());
                
                os.flush();
                os.close();
            } catch (Exception x) {
                x.printStackTrace();
            }
            context.responseComplete();
        
    }
    
    public String getBackupColdPath() {
        return backupColdPath;
    }

    public void setBackupColdPath(String backupColdPath) {
        this.backupColdPath = backupColdPath;
    }
    
    public List<SgaDetails> getSgaDetails() {
        List <SgaDetails> sgaDetails = new ArrayList<SgaDetails>();
        String query = "SELECT name, description, value, display_value "
                + "FROM v$parameter "
                + "WHERE name LIKE '%sga_max_size%' "
                + "OR name LIKE '%sga_target%'";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                SgaDetails sga = new SgaDetails();
                sga.setName(res.getString(1));
                sga.setDescription(res.getString(2));
                sga.setValue(res.getString(3));
                sga.setDisplayValue(res.getString(4));
                sgaDetails.add(sga);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }        
        
        return sgaDetails;
    }
    
    public String getMemoryInfo() {
        String query = "SELECT TRUNC((SUM(f.BYTES) + SUM(s.value))/1024/1024,2) "
                + "FROM dba_data_files f, v$sga s";
        String memorySize = "";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                memorySize = res.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        } 
        
        return memorySize;
    }

    /*
     *
     * REQUETES DEVELOPPEUR
     *
     */
    public List<ObjectOracle> getAllObjectForCurrentUser() {
        allObjectForCurrentUser = new ArrayList<ObjectOracle>();
//        if (this.isDeveloppeur()) {
            String query = "SELECT object_type, count(object_type) AS count "
                    + "FROM obj "
                    + "GROUP BY object_type";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    ObjectOracle objectOracle = new ObjectOracle();
                    String type = res.getString(1);
                    objectOracle.setType(type);
                    objectOracle.setCount(res.getInt(2));
                    objectOracle.setListObject(this.getNameObjectByType(type));
                    allObjectForCurrentUser.add(objectOracle);
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allObjectForCurrentUser;
    }

    public List<String> getNameObjectByType(String type) {
        List<String> nameObjectByType = new ArrayList<String>();
//        if (this.isDeveloppeur()) {
            String query = "SELECT object_name "
                    + "FROM obj "
                    + "WHERE object_type = '"
                    + type
                    + "'";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    nameObjectByType.add(res.getString(1));
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return nameObjectByType;
    }

    public List<TableOracle> getAllTableOracleForCurrentUser() {
        allTableOracleForCurrentUser = new ArrayList<TableOracle>();
//        if (this.isDeveloppeur()) {
            String query = "SELECT table_name, "
                    + "tablespace_name, "
                    + "pct_free, "
                    + "pct_used "
                    + "FROM tabs";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    TableOracle tableOracle = new TableOracle();
                    String tableName = res.getString(1);
                    tableOracle.setName(tableName);
                    tableOracle.setTableSpaceName(res.getString(2));
                    tableOracle.setPctFree(res.getInt(3));
                    tableOracle.setPctUsed(res.getInt(4));
                    tableOracle.setIndexes(this.getAllIndexesByTable(tableName));
                    tableOracle.setCommand(this.getCommandByTable(tableName));
                    allTableOracleForCurrentUser.add(tableOracle);
                }
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allTableOracleForCurrentUser;
    }

    public List<IndexOracle> getAllIndexesByTable(String table) {
        List<IndexOracle> indexes = new ArrayList<IndexOracle>();
        String query = "SELECT index_name, "
                + "index_type, "
                + "table_owner, "
                + "table_name, "
                + "table_type, "
                + "tablespace_name, "
                + "pct_free "
                + "FROM ind "
                + "WHERE table_name='"
                + table
                + "'";

        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                IndexOracle index = new IndexOracle();
                index.setName(res.getString(1));
                index.setType(res.getString(2));
                index.setTableOwner(res.getString(3));
                index.setTableName(res.getString(4));
                index.setTableType(res.getString(5));
                index.setTableSpaceName(res.getString(6));
                index.setPctFree(res.getInt(7));
                indexes.add(index);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return indexes;
    }

    public String getCommandByTable(String table) {
        String query = "SELECT DBMS_METADATA.GET_DDL('TABLE',"
                + "'"
                + table
                + "',"
                + "UPPER('"
                + this.getLogin()
                + "')) "
                + "FROM dual";
        String command = "";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                command += res.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return command;
    }

    public void saveCommandTable() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentTable().getName() + ".txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            String commandTable = this.getCurrentTable().getCommand().replaceAll("\t", "\r\n");
            os.write(commandTable.getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public List<MethodeOracle> getAllMethodeOracleForCurrentUser() {
        allMethodeOracleForCurrentUser = new ArrayList<MethodeOracle>();
//        if (this.isDeveloppeur()) {
            String query = "SELECT name, "
                    + "type "
                    + "FROM user_source "
                    + "WHERE UPPER(type)=UPPER('procedure') "
                    + "OR UPPER(type)=UPPER('function') "
                    + "GROUP BY name, type "
                    + "ORDER BY type";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    MethodeOracle methode = new MethodeOracle();
                    String name = res.getString(1);
                    String type = res.getString(2);
                    methode.setName(name);
                    methode.setType(type);
                    methode.setContent(this.getTextMethodeByNameAndType(name, type));
                    allMethodeOracleForCurrentUser.add(methode);
                }
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allMethodeOracleForCurrentUser;
    }

    public String getTextMethodeByNameAndType(String name, String type) {
        String content = "";
        String query = "SELECT text "
                + "FROM user_source "
                + "WHERE name='"
                + name
                + "' AND type='"
                + type
                + "'";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                content += res.getString(1);
                content += "<br />";
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return content;
    }
    
    public void saveContentMethode() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentMethode().getName() + "_" + this.getCurrentMethode().getType() + ".txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            String contentMethode = this.getCurrentMethode().getContent().replaceAll("<br />", "\r\n");
            os.write(contentMethode.getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public List<TriggerOracle> getAllTriggerOracleForCurrentUser() {
        allTriggerOracleForCurrentUser = new ArrayList<TriggerOracle>();
//        if (this.isDeveloppeur()) {
            String query = "SELECT DISTINCT(name) "
                    + "FROM user_source "
                    + "WHERE type='TRIGGER'";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    TriggerOracle trigger = new TriggerOracle();
                    String name = res.getString(1);
                    String type = "TRIGGER";
                    trigger.setName(name);
                    trigger.setContent(this.getTextMethodeByNameAndType(name, type));
                    trigger.setCommand(this.getCommandByTrigger(name));
                    allTriggerOracleForCurrentUser.add(trigger);
                }
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allTriggerOracleForCurrentUser;
    }

    public String getCommandByTrigger(String trigger) {
        String query = "SELECT DBMS_METADATA.GET_DDL('TRIGGER',"
                + "'"
                + trigger
                + "',"
                + "UPPER('"
                + this.getLogin()
                + "')) "
                + "FROM dual";
        String command = "";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                command += res.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return command;
    }
    
    public void saveCommandTrigger() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentTrigger().getName() + "_TRIGGER" + ".txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            String commandTrigger = this.getCurrentTrigger().getCommand();            
            os.write(commandTrigger.getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }
    
    public List<ViewOracle> getAllViewOracleForCurrentUser() {
        allViewOracleForCurrentUser = new ArrayList<ViewOracle>();
//        if (this.isDeveloppeur()) {
            String query = "SELECT object_name "
                    + "FROM obj "
                    + "WHERE object_type='VIEW'";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    ViewOracle view = new ViewOracle();
                    String name = res.getString(1);
                    view.setName(name);                    
                    view.setCommand(this.getCommandByView(name));
                    allViewOracleForCurrentUser.add(view);
                }
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
//        }
        return allViewOracleForCurrentUser;
    }
    
    public String getCommandByView(String view) {
        String query = "SELECT DBMS_METADATA.GET_DDL('VIEW',"
                + "'"
                + view
                + "',"
                + "UPPER('"
                + this.getLogin()
                + "')) "
                + "FROM dual";
        String command = "";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                command += res.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return command;
    }
    
    public void saveCommandView() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentView().getName() + "_VIEW" + ".txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            String commandView = this.getCurrentView().getCommand();
            os.write(commandView.getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public void createExecutionPlan() {
        this.getExecutionPlan();
    }

    public String getExecutionPlan() {
        String executionPlan = "";
        if (this.getMyQuery() != null && !this.getMyQuery().isEmpty()) {
            /*
             * Test si ; présent ou non
             * Si oui, le ; est supprimé
             */
            if(this.getMyQuery().endsWith(";")) {
                this.myQuery = this.getMyQuery().substring(0, this.getMyQuery().length()-2);
            }
            String query = "EXPLAIN PLAN FOR "
                    + this.getMyQuery();
            String queryExplainPlan = "SELECT * FROM table(dbms_xplan.display)";
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                res = stm.executeQuery(queryExplainPlan);
                executionPlan += "Ma requête : " + this.getMyQuery();
                executionPlan += "<br />";
                executionPlan += "<br />";
                while (res.next()) {
                    executionPlan += res.getString(1);
                    executionPlan += "<br />";
                }
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return executionPlan;
    }
    
    public void saveExecutionPlan() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = "EXECUTION_PLAN" + ".txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();            
            String executionPlanSave = this.getExecutionPlan().replaceAll("<br />", "\r\n");            
            os.write(executionPlanSave.getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public String getMyQuery() {
        return myQuery;
    }

    public void setMyQuery(String myQuery) {
        this.myQuery = myQuery;
    }

    public String getRoleDeConnexion() {
        return roleDeConnexion;
    }

    public void setRoleDeConnexion(String roleDeConnexion) {
        this.roleDeConnexion = roleDeConnexion;
    }

    public boolean isAdmin() {
        if ("Administrateur".equals(this.getRoleDeConnexion())) {
            return true;
        }
        return false;
    }

    public boolean isDeveloppeur() {
        if ("Developpeur".equals(this.getRoleDeConnexion())) {
            return true;
        }
        return false;
    }

    public TableOracle getCurrentTable() {
        return currentTable;
    }

    public void setCurrentTable(TableOracle currentTable) {
        this.currentTable = currentTable;
    }

    public MethodeOracle getCurrentMethode() {
        return currentMethode;
    }

    public void setCurrentMethode(MethodeOracle currentMethode) {
        this.currentMethode = currentMethode;
    }

    public ViewOracle getCurrentView() {
        return currentView;
    }

    public void setCurrentView(ViewOracle currentView) {
        this.currentView = currentView;
    }

    public TriggerOracle getCurrentTrigger() {
        return currentTrigger;
    }

    public void setCurrentTrigger(TriggerOracle currentTrigger) {
        this.currentTrigger = currentTrigger;
    }

    public LoginBean getLoginBean() {
        return loginBean;
    }

    public void setLoginBean(LoginBean loginBean) {
        this.loginBean = loginBean;
    }

    public String getBackupHotPath() {
        return backupHotPath;
    }

    public void setBackupHotPath(String backupHotPath) {
        this.backupHotPath = backupHotPath;
    }
    
    public void saveBackupHotScript() {   
        if(System.getProperty("file.separator").charAt(0) != (this.getBackupHotPath().charAt(this.getBackupHotPath().length()-1))) {
            String backslash= System.getProperty("file.separator");
            backupHotPath += backslash;            
        }
        
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = "saveHot.cmd";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            /*
             * Ecriture dans le fichier
             */        
            
            String exp = "exp userid="
                    + this.getLogin()
                    + "/"
                    + this.getPassword()
                    + " file="
                    + this.getBackupHotPath()
                    + "export_full.dump "
                    + "log="
                    + this.getBackupHotPath()
                    + "export_full.log "
                    + "owner="
                    + this.getLogin();

            os.write(exp.getBytes());            
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }
    
    public void executeHotBackup() {
        if(System.getProperty("file.separator").charAt(0) != (this.getBackupHotPath().charAt(this.getBackupHotPath().length()-1))) {
            String backslash= System.getProperty("file.separator");
            backupHotPath += backslash;            
        }
        
        String exp = "exp userid="
                    + this.getLogin()
                    + "/"
                    + this.getPassword()
                    + " file="
                    + this.getBackupHotPath()
                    + "export_full.dump "
                    + "log="
                    + this.getBackupHotPath()
                    + "export_full.log "
                    + "owner="
                    + this.getLogin();
        try {
            Runtime r = Runtime.getRuntime();
            Process p = r.exec(exp);
            p.waitFor();//si l'application doit attendre a ce que ce process fini
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public String getStartupPath() {
        return startupPath;
    }

    public void setStartupPath(String startupPath) {
        this.startupPath = startupPath;
    }
}
